The invention is directed to a method and system for processing data in order to input data contained in flat files into a relational database.
In the open markets, including mutual fund transactions and trades, substantial amounts of data are often collected by one entity and thereafter communicated to other entities for further processing. Illustratively, the collection entity gathers data regarding trades, purchases or sales and stores this data on a very large mainframe system. At a predetermined time in each business day, the data is downloaded to the secondary entity.
The files that are stored on the mainframe computer of the collection agency are large text files, and specifically what are characterized as xe2x80x9cflat files.xe2x80x9d A flat file is a data file that does not possess any linkages to another file and is not related to another file in any manner. A flat file is commonly used for stand-alone lists. Accordingly, there is commonly no relationship between these flat files nor the records and fields contained in those flat files. By the nature of the flat files, including the layout of the flat files and the data contained in the flat files, it is not possible to effectively utilize the information contained therein. As a result, it is necessary to preprocess the flat files into a relational database. In other words, it is necessary to map the contents of the flat file into a relational database. A relational database is a method of providing a database organization that links files together as is necessary or desired. A relational database system has the ability to create relationships between various files by comparing data in those files. Illustratively, a relational database system has the capability to process two or more files in such a manner that new files are created based on the prior relational files that match requested criteria. Accordingly, it should be appreciated that queries often involve more than one data file. Further, SQL or Structured Query Language, is a language that is used by systems to communicate with a relational database. That is, an operator can create a query using SQL. By using the query, the user requests specific information from within a relational database.
However, it should be appreciated that the layout and content of the flat files may often vary depending on the collecting entity. Further, even with one company for example, that company may change the layout of their flat files for a variety of reasons. The receiving entity must then rework the manner used to process the flat files. Using known techniques, this reformatting causes substantial problems in that the programs utilized to convert the flat files into a relational database are not easily changed. Often, these programs are hard coded. Accordingly, once the format of the incoming flat files is varied, the entire program must again be revised and thereafter recompiled. Accordingly, a need is present to afford enhanced adjustability to deal with the varied layout of flat files, and the processing of these flat files into a relational database.
Thus, there is a particular need for processing flat files for inclusion of the data contained therein in a relational database. Accordingly, the invention provides a system and method for processing flat files for use in a relational database. The invention includes inputting a plurality of flat files wherein each of the flat files contains data. A parameters file is accessed to obtain processing parameters contained in the parameters file. The data in the flat files are checked based on the processing parameters. Thereafter, a logical record is assembled by processing the data in the flat files based on the processing parameters. An output file is generated. The output file contains the logical record and the data obtained from the flat files.
In one aspect of the invention, a method provides for processing flat files for use in a relational database, the method comprising inputting a plurality of flat files, each of the flat files containing data; providing a parameters file containing processing parameters; changing at least one of the processing parameters in the parameters file; accessing the parameters file to obtain the processing parameters, the processing parameters including mandatory file details and optional file details; checking the data in the plurality of the flat files based on the processing parameters, the checking including determining if mandatory files are present based on the mandatory file details and determining if optional files are present based on the optional file details, wherein if all mandatory files are not present, then, the method further including waiting for a predetermined time period and rechecking for the mandatory files; assembling a logical record by processing the data in the flat files based on the processing parameters; and generating an output file, the output file containing the logical record and a portion of the data.
In a further aspect of the invention, a system is provided for processing flat files for use in a relational database, the system comprising an input portion that inputs a plurality of flat files, each of the flat files containing data; a parameters file access portion that accesses a parameters file to obtain processing parameters, the parameters file containing the processing parameters; a data checking portion that checks the data in a plurality of the flat files based on the processing parameters; a logical record generation portion that assembles a logical record by processing the data in the flat files based on the processing parameters; and an output file generation portion that generates an output file, the output file containing the logical record and a portion of the data.
In a further aspect of the invention, a system is provided for processing flat files for use in a relational database, the system comprising an input portion that inputs a plurality of flat files, each of the flat files containing data; a parameters file memory that contains a parameters file, the parameters file containing processing parameters, the processing parameters including mandatory file details and optional file details; a parameters file access portion that accesses the parameters file to obtain processing parameters, the parameters file access portion providing for a user of the system to change the processing parameters; a data checking portion that checks the data in a plurality of the flat files based on the processing parameters, the data checking portion checking if mandatory files are present based on the mandatory file details and checking if optional files are present based on the optional file details; a logical record generation portion that assembles a logical record by processing the data in the flat files based on the processing parameters; and an output file generation portion that generates an output file, the output file containing the logical record and a portion of the data.
In a further aspect of the invention, a system is provided for processing flat files for use in a relational database, the system comprising means for inputting a plurality of flat files, each of the flat files containing data; means for accessing a parameters file to obtain processing parameters, the parameters file containing the processing parameters; means for checking the data in a plurality of the flat files based on the processing parameters; means for assembling a logical record by processing the data in the flat files based on the processing parameters; and means for generating an output file, the output file containing the logical record and a portion of the data.
In a further aspect of the invention, a system is provided for processing flat files for use in a relational database, the system comprising means for inputting a plurality of flat files, each of the flat files containing data; means for providing a parameters file containing processing parameters; means for changing at least one of the processing parameters in the parameters file; means for accessing the parameters file to obtain the processing parameters, the processing parameters including mandatory file details and optional file details; means for checking the data in the plurality of the flat files based on the processing parameters, the checking including determining if mandatory files are present based on the mandatory file details and determining if optional files are present based on the optional file details, wherein if all mandatory files are not present, then, the method further including waiting for a predetermined time period and rechecking for the mandatory files; means for assembling a logical record by processing the data in the flat files based on the processing parameters; and means for generating an output file, the output file containing the logical record and a portion of the data.